package com.orange;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.cncitrus.data.common.Util;

import orange.database.DBIns;

public class Ozcbsyconn extends BaseAction {
	public Ozcbsyconn(String[] str1, String[] str2, String[] str3, String[] str4) throws SQLException {
		super();
		String sql;
		ResultSet rs;
		Connection conn = DBIns.getInstance().getConnection();
		Statement stmt = conn.createStatement();
		sql = change1_4(str1, str2, str3, str4);
		System.out.println(sql);
		rs = stmt.executeQuery(sql);
		value = combine1_4(rs);
		DBIns.getInstance().release(conn);

	}

	public String change1_4(String[] str1, String[] str2, String[] str3,
			String[] str4) {
		try {

			pinzhong = Util.Convert(str1);

			int n = str2.length;
			nianfen = Util.Convert(str2);

//			int s = str3.length;
//			shengfen = Convert(str3);

			xiangmu = Util.Convert(str4);

			 String nnianfen = new String(str2[n - 1].getBytes("ISO-8859-1"),
					 "utf-8");
//			 String sshengfen = new String(str3[s - 1].getBytes("ISO-8859-1"),
//					 "utf-8");
//			String nnianfen = new String(str2[n - 1].getBytes());
//			String sshengfen = new String(str3[s - 1].getBytes());

			String sql = "1";
//
//			if (sshengfen.equals("所有省份")) {
//				if (nnianfen.equals("所有年份")) {
//					sql = "select * from orange_z_cbsy where PinZhong in("
//							+ pinzhong + ")and Class in (" + xiangmu
//							+ ") order by NianFen";
//				} else {
//					sql = "select * from orange_z_cbsy where PinZhong in ("
//							+ pinzhong + ") and NianFen in (" + nianfen
//							+ ")and Class in (" + xiangmu
//							+ ") order by NianFen";
//				}
//			} else {
				if (nnianfen.equals("所有年份")) {
					sql = "select * from orange_z_cbsy where PinZhong in ("
							+ pinzhong + ") and Class in (" + xiangmu
							+ ") order by NianFen";
				} else {
					sql = "select * from orange_z_cbsy where PinZhong in ("
							+ pinzhong + ") and NianFen in(" + nianfen
							+ ") and Class in (" + xiangmu
							+ ") order by NianFen";
				}
//			}
//			System.out.println(sql + "---------------");
			return sql;
		} catch (Exception e) {
//			System.out.println("---------------");
			e.printStackTrace();
			return "error";
		}
	}

	public String combine1_4(ResultSet rs1) {
//		Beforefirst(rs1);
		StringBuffer buffer1 = new StringBuffer();
		buffer1
				.append("<thead><tr><td>年份</td><td>省份</td><td>品种</td><td>具体项目</td><td>单位</td><td>合计</td><td >分类</td></tr></thead>");
//		buffer1.append("<thead><tr><td>具体项目</td></tr></thead>");
		try {
			while (rs1.next()) {
//				String year = rs1.getObject("NianFen").toString().trim();
//				if(Integer.valueOf(year)<2007){
//					
//				}else{
//					buffer1.append("<tr><td colspan=\"7\">"+rs1.getObject("XiangMu")+ "</td></tr>");
//				}
				buffer1.append("<tr><td>" + rs1.getObject("NianFen")
						+ "</td><td>" + rs1.getObject("ShengFen") + "</td><td>"
						+ rs1.getObject("PinZhong") + "</td><td>"
						+ rs1.getObject("XiangMu") + "</td><td>"
						+ rs1.getObject("DanWei") + "</td><td>"
						+ rs1.getObject("HeJi") + "</td><td>"
						+ rs1.getObject("Class") + "</td></tr>");
				
			}
			value = buffer1.toString();
			return value;
		} catch (Exception e) {
			return "-1";
		}
	}
	public static void main(String[] arg){
		try {
			String[] str1={"柑"},str2={"所有年份"},str3={"重庆"},str4={"费用和用工"};
			Ozcbsyconn oz=new Ozcbsyconn(str1, str2, str3, str4);
			System.out.println(oz.getValue());
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

}
